To create a conditional running total

1. To get started, create a report using the sample data, Xtreme.mdb located in the File Data Sources folder of the Data Explorer dialog box. Place the following fields from left to right in the Details section:

{customer.CUSTOMER NAME}
{customer.COUNTRY}
{customer.LAST YEAR'S SALES}

2. On the Report menu, click Sort Records .
3. Sort the records based on the {customer.CUSTOMER NAME} field.
4. On the Insert menu, click Running Total Field.

   The Field Explorer dialog box appears.

5. Double-click Running Total Fields.

   The Create Running Total Field dialog box appears.

6. Enter the name "USTotal" in the Running Total Name box.
7. Highlight {Customer.LAST YEAR'S SALES} in the Available Tables and Fields box, and use the first arrow button to move it over to the Field to summarize box.
8. Select sum from the Type of summary list.
9. In the Evaluate section of the dialog box, click Use a formula and then click the Formula button.

   The Running Total Condition Formula Editor appears.

10. Enter the following formula in the formula box:
    {Customer.Country} = "USA"

    This tells the program to evaluate the running total each time it comes to a record where {Customer.COUNTRY} is equal to "USA." The running total will ignore all other records (such as records for Canada).

11. When the formula has the correct syntax, click Save then Close.

    You return to the Create Running Total Field dialog box.

12. In the Reset section of the dialog box, click Never.
13. Click OK to save the running total field.

    The program returns you to the Field Explorer dialog box.

14. Place the running total field in the Details section of your report.
15. Now create the CanadaTotal running total field using the process outlined in steps 3-13. The only difference is that this time you will set the Evaluate formula to:

{Customer.Country} = "Canada"

16. When you are finished, place the #CanadaTotal field in the Details section of your report, just to the right of the {Customer.LAST YEAR'S SALES} field.
17. If you only want to see a grand total of the Canadian and American sales, place the two running total fields you created in the Report Footer section of your report.